/*
 * DatabaseHelper.java
 * 
 * Copyright©2012-2013 JMAS corporation. All rights reserved.
 */
package jp.co.jmas.jmasvideo.dao;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDoneException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class DatabaseHelper extends SQLiteOpenHelper {
	public static final String DATABASE_NAME = "kindercamera";

	/** Timestamp */
	private static SimpleDateFormat TIMESTAMP_FORMAT;

	private static final String SEPARATOR = ",";

	static {
		TIMESTAMP_FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss", Locale.US);
		TIMESTAMP_FORMAT.setTimeZone(TimeZone.getTimeZone("UTC"));
	}

	protected Context context;

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, 1);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			// STEP-1: Userテーブルの生成
			db.execSQL("create table ClassInfo ("
				+ " key               text    primary key," // User Id
				+ " name              text    not null,"
				+ " kana              text    not null,"
				+ " email             text    not null"
				+ ")");

			db.execSQL("create table Student ("
				+ " key               text    primary key," // User Id
				+ " name              text    not null,"
				+ " kana              text    not null,"
				+ " email             text    not null"
				+ ")");

			// STEP-2: Videoテーブルの生成
			db.execSQL("create table Video ("
				+ " key               text    primary key," // Video Id
				+ " title             text    not null,"
				+ " date              text    not null,"
				+ " duration          integer not null,"
				+ " size              integer not null,"
				+ " expired           text    not null"
//				+ " permissions       text    not null"
				+ ")");

			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// do nothing
	}

	public void setUser(SQLiteDatabase db, Student user) {
		ContentValues values = new ContentValues();
		values.put("key",   user.getKey());
		values.put("name",  user.getName());
		values.put("email", user.getEmail());

		int result = db.update("User", values, "key = ?", new String[] { user.getKey() });
		if (result == 0) {
			db.insert("User", null, values);
		}
	}

	public void setVideo(SQLiteDatabase db, Video video) {
		ContentValues values = new ContentValues();
		values.put("key",         video.getKey());
		values.put("title",       video.getTitle());
		values.put("desc",        video.getDesc());
		values.put("date",        TIMESTAMP_FORMAT.format(video.getDate()));
		values.put("duration",    video.getDuration());
		values.put("size",        video.getSize());
		values.put("expired",     TIMESTAMP_FORMAT.format(video.getDate()));

		int result = db.update("Video", values, "key = ?", new String[] { video.getKey() });
		if (result == 0) {
			db.insert("Video", null, values);
		}
	}

	public Cursor getUserCursor(SQLiteDatabase db) {
		Cursor c = db.query(
			"User",
			new String[] {"key", "name", "email"},
			null, null, null, null, "name");
		return c;
	}

	/**
	 * @param cursor of User
	 * @return
	 */
	public Student getUser(Cursor c) {
		Student user = new Student();
		int i=0;
		user.setKey(c.getString(i++));
		user.setName(c.getString(i++));
		user.setEmail(c.getString(i++));
		return user;
	}

	public Cursor getVideoCursor(SQLiteDatabase db) {
		Cursor c = db.query(
			"Video",
			new String[] {"key", "title", "date", "duration", "size", "permissions"},
			null, null, null, null, "date");
		return c;
	}

	/**
	 * @param cursor of User
	 * @return
	 */
	public Video getVideo(Cursor c) {
		Video video = new Video();
		int i=0;
		video.setKey(c.getString(i++));
		video.setTitle(c.getString(i++));
		try {
			video.setDate(TIMESTAMP_FORMAT.parse(c.getString(i++)));
		} catch (ParseException e) {
			// TODO error handling
		}
		video.setDuration(c.getLong(i++));
		video.setSize(c.getLong(i++));
		return video;
	}

	private static String[] toStringArray(String string) {
		return string.split(SEPARATOR);
	}

	private static String toString(String[] arrays) {
		StringBuilder sb = new StringBuilder();
		for (String string : arrays) {
			sb.append(string);
			sb.append(SEPARATOR);
		}
		return sb.toString();
	}

	public void updateNewVideos(SQLiteDatabase db, File[] files) {
		List<Video> addList = new ArrayList<Video>(); 

		db.beginTransaction();
		try {
			SQLiteStatement stmtSelect = db.compileStatement("SELECT key FROM Video WHERE key = ?");
			for (File file : files) {
				String key = file.getName();
				stmtSelect.bindString(1, key);
				try {
					stmtSelect.simpleQueryForString();
	
				} catch (SQLiteDoneException e) {
					// no records
					Video video = new Video();
					video.setKey(key);
					video.setSize(file.length());
					addList.add(video);
				}
			}
			stmtSelect.close();
	
			if (addList.size() > 0) {
				SQLiteStatement stmtInsert = db.compileStatement(
						"INSERT INTO Video (key, AttributeKey, AttributeValue) VALUES (?,?,?)");
				for (Video video : addList) {
					
				}
				db.setTransactionSuccessful();
			}
		} finally {
			db.endTransaction();

		}
	}

	/**
	 * returns class info with students
	 * @return
	 */
	@Deprecated
	public static List<ClassInfo> getClassInfoList() {
		List<ClassInfo> list = new ArrayList<ClassInfo>();
		list.add(createClassInfo("ばら",
			createStudent("0001", "絵里香", "えりか", Gender.Girl, "kohe5510@gmail.com"),
			createStudent("0002", "和音", "かずね", Gender.Girl, "kohe5510@gmail.com"),
			createStudent("0003", "総", "そう", Gender.Boy, "kohe5510@gmail.com")));

		list.add(createClassInfo("ゆり",
			createStudent("0004", "一清", "かずきよ", Gender.Boy, "kohe5510@gmail.com"),
			createStudent("0005", "蓮華", "れんか",  Gender.Girl, "kohe5510@gmail.com")));

		list.add(createClassInfo("たんぽぽ",
			createStudent("0006", "五月", "めい", Gender.Girl, "kohe5510@gmail.com"),
			createStudent("0007", "夏帆", "かほ", Gender.Girl, "kohe5510@gmail.com")));

		// Studentのクラス情報を更新
		for (ClassInfo classInfo : list) {
			for (Student student : classInfo.getStudentList()) {
				student.setClassInfo(classInfo);
			}
		}
		return list;
	}

	private static ClassInfo createClassInfo(String className, Student... students) {
		ClassInfo classInfo = new ClassInfo();
		classInfo.setName(className);
		classInfo.setStudentList(Arrays.asList(students));
		return classInfo;
	}

	private static Student createStudent(String key, String name, String kana, Gender gender, String email) {
		Student student = new Student();
		student.setKey(key);
		student.setName(name);
		student.setKana(kana);
		student.setGender(gender);
		student.setEmail(email);
		return student;
	}
}